package com.shengruan.admin.jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.shengruan.admin.vo.UserMessage;
import com.shengruan.common.Constants;
import com.shengruan.framework.jdbc.BaseJdbc;
import com.shengruan.framework.jdbc.BeanHandler;
import com.shengruan.framework.jdbc.BeanListHandler;

public class UserMessageJdbc extends BaseJdbc {

    Logger logger = LoggerFactory.getLogger(getClass());

    public static final String USERMESSAGE_INSERT = "INSERT INTO user_message (userId, title, content, STATUS, creationTime)VALUES(?, ?, ?, ?, ?)";

    public static final String USERMESSAGE_UPDATE = "update user_message set userId = ? , title = ? , content = ? , status = ? , creationTime = ? where id = ?";

    /**
     * @param userMessage
     */
    public void save(UserMessage userMessage) {
        Connection conn = this.getConnection();
        Object[] pram = {userMessage.getUserId(), userMessage.getTitle(), userMessage.getContent(), userMessage.getStatus(), userMessage.getCreationTime()};
        try {
            this.execute(conn, USERMESSAGE_INSERT, pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }

    /**
     *
     * @param userId
     * @param title
     * @param content
     */
    public void save(int userId, String title, String content) {

        UserMessage userMessage = new UserMessage();
        userMessage.setUserId(userId);
        userMessage.setTitle(title);
        userMessage.setContent(content);
        userMessage.setStatus(Constants.STATUS_INACTIVE);
        userMessage.setCreationTime(System.currentTimeMillis());

        Connection conn = this.getConnection();
        Object[] pram = {userMessage.getUserId(), userMessage.getTitle(), userMessage.getContent(), userMessage.getStatus(), userMessage.getCreationTime()};
        try {
            this.execute(conn, USERMESSAGE_INSERT, pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }

    /**
     * 
     * @param userMessage
     */
   public void update(UserMessage userMessage) {
        Connection conn = this.getConnection();
        Object[] pram = {userMessage.getUserId(), userMessage.getTitle(), userMessage.getContent(), userMessage.getStatus(), userMessage.getCreationTime(), userMessage.getId()};
        try {
			this.execute(conn, USERMESSAGE_UPDATE, pram);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
    }


    /**
     * 获得用户个数
     *
     * @return
     */
    public int getRows(int userId) {
        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("select count(u.id) from user_message u where u.userId = ? ");
        Object[] pram = {userId};
        int count = 0;
        try {
            count = this.getRows(conn, sql.toString(), pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return count;
    }


    public int getRows(int userId, int status) {
        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("select count(u.id) from user_message u where u.userId = ? and u.status = ? ");
        Object[] pram = {userId, status};
        int count = 0;
        try {
            count = this.getRows(conn, sql.toString(), pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return count;
    }

    /**
     * @param pageSize
     * @param currentPage
     * @param userId
     * @return
     */
    public List<UserMessage> findWithPage(int currentPage, int pageSize, int userId) {
        List<UserMessage> userMessageList = new ArrayList<UserMessage>();
        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("select * from user_message u where u.userId ="+userId+" order by u.creationTime desc limit ?,?");
        try {
            int start = (currentPage - 1) * pageSize;
            userMessageList = this.query(conn, sql.toString(), new BeanListHandler<UserMessage>(UserMessage.class), start, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return userMessageList;
    }


    /**
     * 根据ID获得用户信息
     *
     * @param id
     * @return
     */
    public UserMessage getById(int id) {
        Connection conn = this.getConnection();
        UserMessage user = null;
        try {
            user = this.query(conn, "select * from user_message where id = ?", new BeanHandler<UserMessage>(UserMessage.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
		}
		return user;
	}

    /**
     * 根据ID删除
     * @param id
     */
    public void deleteById(int id, int userId){
        Connection conn = this.getConnection();
		StringBuffer sql = new StringBuffer("delete from user_message where id = ? and userId = ?");
        Object[] pram = {id , userId};
		try {
			this.execute(conn, sql.toString(), pram);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
    }
}
